Mastering SQL for Oracle Fusion HCM – Chapter 12. SQL Joins and Advanced Join Techniques in Fusion HCM

 

✅Chapter 12. SQL Joins and Advanced Join Techniques in Fusion HCM


SQL Joins and Advanced Join Techniques in Oracle Fusion HCM – Combining Data from Multiple Tables 

 Master SQL joins and advanced techniques to combine data across multiple tables in Oracle Fusion HCM. Learn about INNER, LEFT, RIGHT, and FULL JOINs with real-world examples.

🔹 Introduction

SQL joins allow you to combine data from multiple tables based on a related column. This is especially important in Oracle Fusion HCM, where employee data is distributed across different tables (e.g., assignments, jobs, and departments). Understanding how to use joins effectively helps you retrieve meaningful insights by linking relevant pieces of data.

This chapter explains different types of SQL joins, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, along with their use cases in real-world Fusion HCM scenarios.

🔹 Theoretical Concepts

📌 What is a SQL Join?

A SQL JOIN combines rows from two or more tables based on a related column. There are several types of joins depending on how you want to match data:

  1. INNER JOIN: Returns only the rows that have matching values in both tables.

  2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table, or NULL if there is no match.

  3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matched rows from the left table, or NULL if there is no match.

  4. FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in either the left or right table. Non-matching rows from both tables will show NULLs.

📌 Basic Syntax for Joins

SELECT COLUMNS
FROM   table1 join_type table2
ON table1.COLUMN = table2.COLUMN

📌 Types of Joins

1. INNER JOIN

  • Combines rows from both tables where there is a match.


SELECT *
FROM   employees e
       inner join departments d
               ON e.department_id = d.department_id 

2. LEFT JOIN

  • Returns all rows from the left table and matching rows from the right table.


SELECT *
FROM   employees e
       left join departments d
              ON e.department_id = d.department_id 

3. RIGHT JOIN

  • Returns all rows from the right table and matching rows from the left table.


SELECT *
FROM   employees e
       right join departments d
               ON e.department_id = d.department_id 

4. FULL JOIN

  • Returns rows from both tables, with NULLs where there is no match.


SELECT *
FROM   employees e
       full join departments d
              ON e.department_id = d.department_id 

🔹 Using Joins in SQL for Fusion HCM

✅ Example 1: INNER JOIN to Combine Employee and Department Data

Scenario: Retrieve a list of employees along with their department names.


SELECT e.person_number,
       e.full_name,
       d.department_name
FROM   per_all_people_f e
       inner join hr_all_departments d
               ON e.department_id = d.department_id
WHERE  TRUNC(SYSDATE) BETWEEN e.effective_start_date AND e.effective_end_date
       AND TRUNC(SYSDATE) BETWEEN d.effective_start_date AND
                                  d.effective_end_date 

✅ Example 2: LEFT JOIN for Employees with or without Departments

Scenario: Retrieve a list of employees and their department names, including employees who don’t belong to any department.


SELECT e.person_number,
       e.full_name,
       d.department_name
FROM   per_all_people_f e
       left join hr_all_departments d
              ON e.department_id = d.department_id
WHERE  TRUNC(SYSDATE) BETWEEN e.effective_start_date AND e.effective_end_date 

✅ Example 3: RIGHT JOIN to Find Departments with or without Employees

Scenario: Retrieve a list of all departments, including those without employees.


SELECT d.department_name,
       e.full_name
FROM   hr_all_departments d
       right join per_all_people_f e
               ON d.department_id = e.department_id
WHERE  TRUNC(SYSDATE) BETWEEN d.effective_start_date AND d.effective_end_date 

✅ Example 4: FULL JOIN for Employees and Departments with Missing Data

Scenario: Retrieve a list of employees and departments, including those with no matching data.


SELECT e.full_name,
       d.department_name
FROM   per_all_people_f e
       full join hr_all_departments d
              ON e.department_id = d.department_id 

🔹 Real-Time Scenario (Fusion HCM Reporting)

🧾 Scenario:

“Create a report showing employees, their department names, and their job titles, including employees who have no department or job title.”

✅ SQL Query:


 SELECT e.person_number,
       e.full_name,
       d.department_name,
       j.job_name
FROM   per_all_people_f e,
       hr_all_departments d,
       per_jobs j
WHERE  TRUNC(SYSDATE) BETWEEN e.effective_start_date AND e.effective_end_date
       AND e.department_id = d.department_id
       AND e.job_id = j.job_id 

🔹 Advanced Join Techniques

✅ Example 5: Self Join to Compare Employee Salaries

Scenario: Compare the salary of each employee with the salary of employees in the same department.

SELECT e1.person_number,
       e1.full_name,
       e1.salary,
       e2.salary AS department_salary
FROM   per_all_assignments_m e1,
       per_all_assignments_m e2
WHERE  e1.person_number != e2.person_number
       AND e1.department_id = e2.department_id 

✅ Example 6: Join with Subquery to Filter Data

Scenario: Retrieve a list of employees whose salary is greater than the average salary in their department.


SELECT e.person_number,
       e.full_name,
       e.salary
FROM   per_all_assignments_m e,
       (SELECT department_id,
               AVG(salary) AS avg_salary
        FROM   per_all_assignments_m
        GROUP  BY department_id) avg_salaries
WHERE  e.salary > avg_salaries.avg_salary
       AND e.department_id = avg_salaries.department_id 

🔹 Best Practices for Using Joins in Fusion HCM

  • Use INNER JOIN when you only want rows that match in both tables.

  • LEFT JOIN is useful for retrieving all rows from the left table, even if there’s no match in the right table (e.g., employees without departments).

  • Avoid using FULL JOIN unless absolutely necessary, as it can return a large number of NULL values, potentially impacting performance.

  • When dealing with large datasets in Fusion HCM, always ensure your joins are indexed to improve performance.

🔹 Summary

✅ SQL Joins are critical for combining data from multiple tables in Fusion HCM.
✅ INNER JOIN returns only matched rows, while LEFT JOIN and RIGHT JOIN include unmatched rows from one side.
✅ FULL JOIN returns all rows from both tables, with NULLs for non-matching records.
✅ Advanced techniques, like self joins and joins with subqueries, are used to compare and filter data efficiently.

🔹 Next Steps

Tags: #SQLJoins, #FusionHCM, #LeftJoin, #RightJoin, #FullJoin, #InnerJoin, #SelfJoin, #AdvancedSQL

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)